package masterlink.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import masterlink.bean.OrdersBean;
import tw.fatsung.db.DbManager;

public class OrdersDAO {
	
	private static final Logger log=LoggerFactory.getLogger(OrdersDAO.class);
	private static final long serialVersionUID=1L;
	private static final String pipe="|";
	private static final String sqlq="select o.*,round(c.weight*100) weight from orders as o, classify as c "+ 
			"where o.stock_id=c.stock_id and date=? and category=? order by time1";
	private static final String sqlqt="select o.*,round(c.weight*100) weight from orders as o, classify as c "+ 
			"where o.stock_id=c.stock_id "+ 
			"and date=? and category=? "+ 
			"and time1 >= ? and time1 < ? ";
	private static final String sqlqg="select date,tag,count(*) cnt "+
			"from orders as o, classify as c where o.stock_id = c.stock_id "+
			"and category=? group by date,tag order by date desc ";
	public List<OrdersBean> findGroup(int category) throws Exception{
		List<OrdersBean> list = new ArrayList<OrdersBean>();
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs=null;
		StringBuffer buf=new StringBuffer();
		try{
			conn=DbManager.getConnection();
			pstmt=conn.prepareStatement(sqlqg);
			pstmt.setInt(1, category);
			rs=pstmt.executeQuery();
			while(rs.next()){
				buf.append(rs.getInt("date"));
				buf.append(pipe);
				buf.append(rs.getInt("tag"));
				buf.append(pipe);
				buf.append(rs.getInt("cnt"));
				buf.append(pipe);
				//buf+=rs.getInt("date")+pipe+rs.getInt("tag")+pipe+rs.getInt("cnt")+pipe;
			}
			String a[]=buf.toString().split("[|]");
			for(int i=0;i<a.length;i=i+9){
				OrdersBean sb = new OrdersBean();
				sb.setDate(Integer.parseInt(a[i]));
				for(int j=1;j<=7;j=j+3){
					switch(a[i+j].charAt(0)){
					case '0':
						sb.setTot(Integer.parseInt(a[i+j+1]));
						break;
					case '1':
						sb.setBsum(Integer.parseInt(a[i+j+1]));
						break;
					case '2':
						sb.setSsum(Integer.parseInt(a[i+j+1]));
						break;
					}
				}
				list.add(sb);
			}
		}catch (Exception e) {
			log.error("OrdersDAO查錯誤!",e);
			throw e;
		}finally{
			DbManager.close(rs);
			DbManager.close(pstmt);
			DbManager.close(conn);
		}	
		return list;
	}
	
	public List<OrdersBean> find(int date,int category) throws Exception{
		List<OrdersBean> list = new ArrayList<OrdersBean>();
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs=null;
		try{
			conn=DbManager.getConnection();
			pstmt=conn.prepareStatement(sqlq);
			pstmt.setInt(1, date);
			pstmt.setInt(2, category);
			rs=pstmt.executeQuery();
			while(rs.next()){
				OrdersBean sb = new OrdersBean();
				sb.setStock_id(rs.getString("stock_id"));
				sb.setTime1(rs.getInt("time1"));
				sb.setTime2(rs.getInt("time2"));
				sb.setDprice(rs.getInt("dprice"));
				sb.setTot(rs.getInt("totSum"));
				sb.setBsum(rs.getInt("bsum"));
				sb.setSsum(rs.getInt("ssum"));
				sb.setAvg(rs.getInt("avg"));
				sb.setTag(rs.getInt("tag"));
				sb.setWeight(rs.getInt("weight"));
				list.add(sb);
			}
		}catch (Exception e) {
			log.error("OrdersDAO查錯誤!",e);
			throw e;
		}finally{
			DbManager.close(rs);
			DbManager.close(pstmt);
			DbManager.close(conn);
		}	
		return list;
	}
	public List<OrdersBean> find(int date,int category,int stime,int etime) throws Exception{
		List<OrdersBean> list = new ArrayList<OrdersBean>();
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs=null;
		try{
			conn=DbManager.getConnection();
			log.trace("date={}, category={}",date,category);
			log.trace("stime={}, etime={}",stime,etime);
			pstmt=conn.prepareStatement(sqlqt);
			pstmt.setInt(1, date);
			pstmt.setInt(2, category);
			pstmt.setInt(3,	stime);
			pstmt.setInt(4,	etime);
			rs=pstmt.executeQuery();
			while(rs.next()){
				OrdersBean sb = new OrdersBean();
				sb.setStock_id(rs.getString("stock_id"));
				sb.setTime1(rs.getInt("time1"));
				sb.setTime2(rs.getInt("time2"));
				sb.setDprice(rs.getInt("dprice"));
				sb.setTot(rs.getInt("totSum"));
				sb.setBsum(rs.getInt("bsum"));
				sb.setSsum(rs.getInt("ssum"));
				sb.setAvg(rs.getInt("avg"));
				sb.setTag(rs.getInt("tag"));
				sb.setWeight(rs.getInt("weight"));
				list.add(sb);
			}
		}catch (Exception e) {
			log.error("OrdersDAO查錯誤!",e);
			throw e;
		}finally{
			DbManager.close(rs);
			DbManager.close(pstmt);
			DbManager.close(conn);
		}	
		return list;
	}
}
